package com.inei.cenacom.model.connection;

import java.io.InputStream;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.NodeList;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.inei.cenacom.R;

public class UtilBD extends SQLiteOpenHelper {
	private static final String DB = "CENACOM.db";
	private static final int VERSION = 3;
	Context context;

	public UtilBD(Context context) {
		// TODO Auto-generated constructor stub
		super(context, DB, null, VERSION);
		Log.i("util", "constructor");
		this.context = context;
		// crearBD();
	}

	public void crearBD(SQLiteDatabase db) {
		Log.i("util", "create");
		String sql;
		InputStream in;
		Document doc;
		NodeList statements;
		int i;
		try {
			DocumentBuilder dbuilder = DocumentBuilderFactory.newInstance()
					.newDocumentBuilder();
			// Marco
			in = context.getResources().openRawResource(R.raw.marco);
			doc = dbuilder.parse(in, null);
			statements = doc.getElementsByTagName("Marco");
			for (i = 0; i < statements.getLength(); i++) {
				sql = statements.item(i).getChildNodes().item(0).getNodeValue();
				db.execSQL(sql);
			}

			// Login
			in = context.getResources().openRawResource(R.raw.sqllogin);
			doc = dbuilder.parse(in, null);
			statements = doc.getElementsByTagName("Login");
			for (i = 0; i < statements.getLength(); i++) {
				sql = statements.item(i).getChildNodes().item(0).getNodeValue();
				db.execSQL(sql);
			}

			// Departamento
			in = context.getResources().openRawResource(R.raw.sqldep);
			doc = dbuilder.parse(in, null);
			statements = doc.getElementsByTagName("Dep");
			for (i = 0; i < statements.getLength(); i++) {
				sql = statements.item(i).getChildNodes().item(0).getNodeValue();
				db.execSQL(sql);
			}

			// Provincia
			in = context.getResources().openRawResource(R.raw.sqlpro);
			doc = dbuilder.parse(in, null);
			statements = doc.getElementsByTagName("Pro");
			for (i = 0; i < statements.getLength(); i++) {
				sql = statements.item(i).getChildNodes().item(0).getNodeValue();
				db.execSQL(sql);
			}

			// Distrito
			in = context.getResources().openRawResource(R.raw.sqldis);
			doc = dbuilder.parse(in, null);
			statements = doc.getElementsByTagName("Dis");
			for (i = 0; i < statements.getLength(); i++) {
				sql = statements.item(i).getChildNodes().item(0).getNodeValue();
				db.execSQL(sql);
			}

			// Caratula
			in = context.getResources().openRawResource(R.raw.sqlcaratula);
			doc = dbuilder.parse(in, null);
			statements = doc.getElementsByTagName("Caratula");
			for (i = 0; i < statements.getLength(); i++) {
				sql = statements.item(i).getChildNodes().item(0).getNodeValue();
				db.execSQL(sql);
			}

			// cap
			in = context.getResources().openRawResource(R.raw.sqlcap);
			doc = dbuilder.parse(in, null);
			statements = doc.getElementsByTagName("cap");
			for (i = 0; i < statements.getLength(); i++) {
				sql = statements.item(i).getChildNodes().item(0).getNodeValue();
				db.execSQL(sql);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		Log.i("util", "create");
		crearBD(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String sql;
		InputStream in;
		Document doc;
		NodeList statements;
		int i;

		for (int upgradeTo = oldVersion + 1; upgradeTo <= newVersion; upgradeTo++) {
			switch (upgradeTo) {
			case 3:
				try{
				db.execSQL("delete from LOGIN;");
				DocumentBuilder dbuilder = DocumentBuilderFactory.newInstance()
						.newDocumentBuilder();
				// Login
				in = context.getResources().openRawResource(R.raw.sqllogin);
				doc = dbuilder.parse(in, null);
				statements = doc.getElementsByTagName("Login");
				for (i = 0; i < statements.getLength(); i++) {
					sql = statements.item(i).getChildNodes().item(0).getNodeValue();
					db.execSQL(sql);
				}
				}catch (Exception e) {
e.printStackTrace();
				}
				break;

			
			default:
				break;
			}
		}
	}

}
